﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using ToolsLayer;
using BusinessEntity;
using Webcar.BLL;
namespace Webcar.Car
{
    public partial class RunRecordQuery : PageBase
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            GridViewStyler gvs = new GridViewStyler(this.GridView1);
            gvs.SetGridHover = false;
            gvs.SetStyle();
            if (this.IsPostBack)
                return;
            
        }

        public string CreateQuery()
        {
            string sql = @"select t.dept_code,t2.dept_name,
                            sum(carnums_01) as carnums_01,
                            sum(carnums_02) as carnums_02,
                            sum(carnums_03) as carnums_03,
                            sum(month_01) as month_01,
                            sum(month_02) as month_02,
                            sum(month_03) as month_03,
                            sum(isnull(carnums_01,0)-isnull(month_01,0)) as nocheck_01,
                            sum(isnull(carnums_02,0)-isnull(month_02,0))  as nocheck_02,
                            sum(isnull(carnums_03,0)-isnull(month_03,0))as nocheck_03
                            from 
                            (
                            select b.dept_code,
                            sum(case when convert(varchar(7),b.buy_date,120)<@year+case when len(cast(1+@year2 as varchar))=1 then '-0' else '-' end+cast(1+@year2 as varchar) then 1 else 0 end) carnums_01,
                            sum(case when convert(varchar(7),b.buy_date,120)<@year+case when len(cast(2+@year2 as varchar))=1 then '-0' else '-' end+cast(2+@year2 as varchar) then 1 else 0 end) carnums_02,
                            sum(case when convert(varchar(7),b.buy_date,120)<@year+case when len(cast(3+@year2 as varchar))=1 then '-0' else '-' end+cast(3+@year2 as varchar) then 1 else 0 end) carnums_03,
                            0 month_01,0 month_02,0 month_03
                             from v_bs_car_record b
                            where b.is_use='T' and b.car_class2 ='TZ'
                            group by b.dept_code
                            union all
                            select t.dept_code,
                            0,0,0,
                            sum(case run_date when @year+case when len(cast(1+@year2 as varchar))=1 then '-0' else '-' end+cast(1+@year2 as varchar) then 1 else 0 end) month_01,
                            sum(case run_date when @year+case when len(cast(2+@year2 as varchar))=1 then '-0' else '-' end+cast(2+@year2 as varchar) then 1 else 0 end) month_02,
                            sum(case run_date when @year+case when len(cast(3+@year2 as varchar))=1 then '-0' else '-' end+cast(3+@year2 as varchar) then 1 else 0 end) month_03
                            from (select distinct child_dept,dept_code,dept_name, car_no,convert(varchar(7),run_date,120) run_date from v_run_record) t 
                            where 
                            1=1
                            group by t.dept_code) as t,
                            dept t2 where t.dept_code=t2.dept_code and isnull(t2.del_flag,'F')='F'
                            group by t.dept_code,t2.dept_name";
            return sql;
        }
        public void SearchClick(object sender, EventArgs e)
        {
            IDataParameter p = DB.helper.GetParameter("@year", this.SelectByYearQuar1.GetYear());
            IDataParameter p2 = DB.helper.GetParameter("@year2", this.SelectByYearQuar1.GetQuarter());
            IDataParameter p3 = DB.helper.GetParameter("@user", CurrentUser.LoginUserID);
            DataSet ds = DB.ExecuteDataset(this.CreateQuery(), p, p2, p3);
            //为未检查数加链接

            DataRow sum_row = ds.Tables[0].NewRow();
            sum_row["dept_code"] = "sum";
            sum_row["dept_name"] = "合计：";

            foreach (DataColumn column in ds.Tables[0].Columns)
            {
                if (column.ColumnName.ToLower() == "dept_code" || column.ColumnName.ToLower() == "dept_name")
                {
                    continue;
                }
                
                sum_row[column] = ds.Tables[0].Compute("sum(" + column.ColumnName + ")", "");
            }
            ds.Tables[0].Rows.Add(sum_row);
            ds.Tables[0].Columns.Add("nocheck_01_url");
            ds.Tables[0].Columns.Add("nocheck_02_url");
            ds.Tables[0].Columns.Add("nocheck_03_url");
            sum_row["nocheck_01_url"] = sum_row["nocheck_01"];
            sum_row["nocheck_02_url"] = sum_row["nocheck_02"];
            sum_row["nocheck_03_url"] = sum_row["nocheck_03"];

            foreach(DataRow row in ds.Tables[0].Rows)
            {
                if (row["dept_code"].ToString() == "sum") continue;
                for (int i = 1; i < 4; i++)
                {
                    int carnums = int.Parse(row["carnums_0"+i.ToString()].ToString());
                    int nochecknums = int.Parse(row["nocheck_0"+i.ToString()].ToString());
                    if (nochecknums > 0)
                    {
                        int q = int.Parse(this.SelectByYearQuar1.GetQuarter());
                        string url = "RunRecordList.aspx?dept={0}&month={1}";
                        int m = q + i;
                        string month = this.SelectByYearQuar1.GetYear()+"-"+m.ToString().PadLeft(2,'0');
                        url = string.Format(url, row["dept_code"].ToString(), month);
                        string t = "<a href='{0}'><font color=red>{1}</font></a>";
                        row["nocheck_0" + i.ToString()+"_url"] = string.Format(t, url, nochecknums.ToString());
                    }
                }
            }
            this.SetHeader();
            //添加合计
            
            this.GridView1.DataSource = ds;
            this.GridView1.DataBind();
            
        }
        private void SetHeader()
        {
            int q = int.Parse(this.SelectByYearQuar1.GetQuarter());
            for(int i=1;i<this.GridView1.Columns.Count;i++)
            {
                double d = (i-1) / 3;

                int s = int.Parse(Math.Ceiling(d).ToString());
                string m = (q+s+1).ToString();
                string t = this.GridView1.Columns[i].HeaderText;
                int pos = t.IndexOf("月");
                if (pos > 0)
                {
                    t = t.Substring(2);
                }
                this.GridView1.Columns[i].HeaderText = m + "月" + t;
            }
        }
    }
}
